package com.bw.dao;

import com.bw.bean.Emp;
import com.bw.bean.Emp2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface EmpDao {

    @Select("select \n" +
            "b.name,\n" +
            "sum(o.final_total_amount) `value`\n" +
            "from order_info o\n" +
            "LEFT JOIN base_province b on o.province_id=b.id\n" +
            "group by b.name")
    List<Emp> getList1();

    @Select("select \n" +
            "b.name,\n" +
            "sum(oi.final_total_amount) `value`\n" +
            "from order_info oi\n" +
            "LEFT JOIN order_detail od on oi.id=od.order_id\n" +
            "LEFT JOIN sku_info k on od.sku_id=k.id\n" +
            "LEFT JOIN base_trademark b on k.tm_id=b.id\n" +
            "where b.name is not null\n" +
            "group by b.name")
    List<Emp> getList2();

    @Select("select \n" +
            "c1.name,\n" +
            "sum(oi.final_total_amount) `value`\n" +
            "from order_info oi\n" +
            "LEFT JOIN order_detail od on oi.id=od.order_id\n" +
            "LEFT JOIN sku_info k on od.sku_id=k.id\n" +
            "LEFT JOIN base_category3 c3 on k.category3_id=c3.id\n" +
            "LEFT JOIN base_category2 c2 on c3.category2_id=c2.id\n" +
            "LEFT JOIN base_category1 c1 on c2.category1_id=c1.id\n" +
            "where c1.name is not null\n" +
            "group by c1.name")
    List<Emp> getList3();

    @Select("select '老用户' `name`,count(*) 'value' from user_info where TIMESTAMPDIFF(MONTH,create_time,NOW()) >=1\n" +
            "UNION\n" +
            "select '新用户' `name`,count(*) 'value' from user_info where TIMESTAMPDIFF(MONTH,create_time,NOW()) <1\n")
    List<Emp> getList4();

    @Select("select \n" +
            "CONCAT(SUBSTR(birthday,3,1),'0后') `name`,\n" +
            "sum(oi.final_total_amount) `value`\n" +
            "from user_info ui\n" +
            "LEFT JOIN order_info oi on oi.user_id=ui.id\n" +
            "group by CONCAT(SUBSTR(birthday,3,1),'0后')")
    List<Emp> getList5();

    @Select("select \n" +
            "yys `name`,\n" +
            "sum(final_total_amount) `value`\n" +
            "from order_info\n" +
            "group by yys")
    List<Emp> getList6();

    @Select("select \n" +
            "bt.name,\n" +
            "if(ui.gender='M','男','女') `gender`,\n" +
            "sum(oi.final_total_amount) `value`\n" +
            "from order_info oi\n" +
            "LEFT JOIN user_info ui ON oi.user_id=ui.id\n" +
            "LEFT JOIN order_detail od ON od.order_id=oi.id\n" +
            "LEFT JOIN sku_info sk on od.sku_id=sk.id\n" +
            "LEFT JOIN base_trademark bt ON sk.tm_id=bt.id\n" +
            "where bt.name is not null\n" +
            "group by bt.name,ui.gender")
    List<Emp2> getList7();

    @Select("select \n" +
            "bt.name,\n" +
            "sum(oi.final_total_amount) `value`\n" +
            "from order_info oi\n" +
            "LEFT JOIN order_detail od ON od.order_id=oi.id\n" +
            "LEFT JOIN sku_info sk on od.sku_id=sk.id\n" +
            "LEFT JOIN base_trademark bt ON sk.tm_id=bt.id\n" +
            "where bt.name is not null\n" +
            "group by bt.name")
    List<Emp> getList8();

    @Select("select \n" +
            "sp.spu_name `name`,\n" +
            "sum(oi.final_total_amount) `value`\n" +
            "from order_info oi \n" +
            "LEFT JOIN order_detail od ON od.order_id=oi.id\n" +
            "LEFT JOIN sku_info sk ON od.sku_id=sk.id\n" +
            "LEFT JOIN spu_info sp ON sk.spu_id=sp.id\n" +
            "where sp.spu_name is not null\n" +
            "group by sp.spu_name")
    List<Emp> getList9();

    @Select("CALL getZ();")
    Double getList10();
}
